if exists (select 1 from sysobjects where name = 'get_planrow' and type = 'P')
begin
   drop procedure get_planrow
   print 'Procedure: get_planrow deleted ...'
end
go
create procedure get_planrow(
  @planid            int = 27,
  @prsschemaid       int = 2,
  @prsid             int = 1
)
as
begin
  set nocount on

   declare @stundenprotag int
--   declare @prsid int
   declare @abteilungid int
   declare @datumvon datetime
   declare @mediandate datetime

   create table #dienste(
     datum    datetime,
     name     varchar(32),
     dienstid int,
     stunden  decimal(4,2),
     rgb      int,
     colorname varchar(32),
     typ      int
   )
   
   set @datumvon = (select Von from ArbeitsPlan where PlanID = @planid)
   set @mediandate = convert(datetime,(select convert(varchar,(datepart(yyyy,@datumvon)))+
                                       case when datepart(MM,@datumvon) <10 
                                            then '0'+convert(varchar,(datepart(MM,@datumvon)))+'15'
                                            else convert(varchar,(datepart(MM,@datumvon)))+'15'
                                             end),112)

   select @stundenprotag = stundenprotag
     from PlanSchema ps
    inner join ArbeitsPlan ap
       on ap.SchemaID = ps.SchemaID
    inner join Abteilung a
       on a.AbteilungID = ps.AbteilungID
    where ap.PlanID = @planid
      and ps.PrsSchemaID = @prsschemaid

   select @abteilungid = AbteilungID
     from PlanSchema ps
    inner join ArbeitsPlan ap
       on ap.SchemaID = ps.SchemaID
    where ap.PlanID = @planid
      and ps.PrsSchemaID = @prsschemaid

--   select @prsid = PrsID
--     from PlanSchema ps
--    inner join ArbeitsPlan ap
--       on ap.SchemaID = ps.SchemaID
--    where ap.PlanID = @planid
--      and ps.PrsSchemaID = @prsschemaid

   insert #dienste
   select datum    = Datum,
          name     = d.Name,
          dienstid = dp.DienstID,
          stunden  = 0,
          rgb      = d.RGB,
          colorname = d.ColorName,
          typ      = d.Typ
     from ArbeitsPlan ap
    inner join PlanSchema ps
       on ps.SchemaID = ap.SchemaID
     left outer join Person p
       on p.PrsId = ps.PrsID
    inner join Abteilung a
       on a.AbteilungID = ps.AbteilungID
    inner join Dienstplan dp
       on dp.PlanID = ap.PlanID
      and dp.PrsID = ps.PrsID
      and dp.AbteilungID = ps.AbteilungID
    inner join Dienst d
       on d.DienstID = dp.DienstID
    where ap.PlanID = @planid
      and ps.PrsSchemaID = @prsschemaid
    order by PrsSchemaID

   insert #dienste
   select datum    = Datum,
          name     = d.Name,
          dienstid = dp.DienstID,
          stunden  = 0,
          rgb      = d.RGB,
          colorname = d.ColorName,
          typ      = d.Typ
     from ArbeitsPlanzprs ap
     left outer join Person p
       on p.PrsId = ap.zprsID
    inner join Abteilung a
       on a.AbteilungID = ap.abteilungID
    inner join Dienstplan dp
       on dp.PlanID = ap.planID
      and dp.PrsID = ap.zprsID
      and dp.AbteilungID = ap.abteilungID
    inner join Dienst d
       on d.DienstID = dp.DienstID
    where ap.planID = 24
      and ap.zprsID = @prsid

-- zeiten
   update #dienste
      set stunden = @stundenprotag
     from #dienste
    inner join Dienst d
       on #dienste.dienstid = d.DienstID
    inner join DienstZeit dz
       on dz.DienstID = d.DienstID
      and dz.datvon <= @mediandate
      and dz.datbis > @mediandate
      and dz.abteilungzeit = 1

   update #dienste
      set stunden = convert(varchar(2),datepart(hh, Bis - Von)) + '.' + convert(varchar(2),datepart(n, Bis - Von))
     from #dienste
    inner join Dienst d
       on #dienste.dienstid = d.DienstID
    inner join DienstZeit dz
       on dz.DienstID = d.DienstID
      and dz.datvon <= @mediandate
      and dz.datbis > @mediandate
      and dz.abteilungzeit <> 1

-- farben
   update #dienste
      set rgb = ARGB,
          colorname = cp.Colorname
     from #dienste, Colorplan cp
    where cp.PlanID = @planid
      and cp.Datum = #dienste.datum
      and cp.PrsID = @prsid
      and cp.AbteilungID = @abteilungid


   insert #dienste
   select datum    = Datum,
          name     = '',
          dienstid = 0,
          stunden  = 0,
          rgb      = ARGB,
          colorname = Colorname,
          typ      = 0
     from Colorplan
    where Datum not in (select datum from #dienste)
      and PlanID = @planid
      and PrsID = @prsid
      and AbteilungID = @abteilungid

-- select * from ColorPlan where planid = 9

   select *
     from #dienste
    order by datum

end
go
print 'Procedure: get_planrow done ...'
go

grant exec on get_planrow to prsadmins with grant option
go
grant exec on get_planrow to prsusers
go

